# -*- coding: utf-8 -*-
# author: AZJ
# date: 2024/11/13
# 假如命运扼住了你的咽喉，你就去挠她的咯吱窝
import pymysql
import openpyxl
"""
登录数据
期望数据
（数据库获取/excel/yml/py）
"""

class OpData:
    def open_xl(self, file):
        wb = openpyxl.load_workbook(f'{file}.xlsx')
        sheet_name = wb.sheetnames[0]
        sheet = wb[sheet_name]
        result = []
        for row in sheet.iter_rows():
            tmp = []
            for cell in row:
                tmp_v = cell.value
                if cell.value is None:
                    tmp_v = ''
                tmp.append(tmp_v)
            result.append(tmp)
        return result

    def db_data(self, table_name, want_col, column_name, value):
        config = {
            'host': 'localhost',
            'port': 3306,
            'user': 'root',
            'password': 'root',
            'database': 'finance',
            'charset': 'utf8'
        }
        conn = pymysql.connect(**config)
        cur = conn.cursor()
        sql = ""
        if type(value) is int:
            sql = 'select %s from %s where %s=%s' % (want_col, table_name, column_name, value)
        else:
            sql = 'select %s from %s where %s="%s"' % (want_col, table_name, column_name, value)
        print(sql)
        cur.execute(sql)
        result = cur.fetchall()
        return result

    def write_xl(self, file, data):
        wb = openpyxl.Workbook()
        ws = wb.active
        for row in range(len(data)):
            for col in range(len(data[row])):
                ws.cell(row=row+1, column=col+1).value = data[row][col]
        wb.save(f'{file}.xlsx')

op_data = OpData()